<?php
	class Schools extends CI_Model{

	 	function __construct(){
          	parent::__construct();
      	}

        function get_school_all(){
            $sql = "select id,school_name from {$this->db->dbprefix}o2o_school";
            return $this->db->query($sql)->result_array();
        }

        function get_school_list($limit = 0, $offset = 0, $conditions = array()){
            $where = "1 = 1";
            if($conditions){
                if( ($conditions['status'] == 0 || $conditions['status'] == 1 || $conditions['status'] == 2) && $conditions['status'] != 9){
                    $where .= " AND s.status = {$conditions['status']}";
                }
                if(!empty($conditions['school_name'])){
                    $where .= " AND s.school_name LIKE '%{$conditions['school_name']}%'";
                }
                if(!empty($conditions['agent_id'])){
                    $where .= " AND s.agent_id = '{$conditions['agent_id']}'";
                }
            }
            $sql = "SELECT SQL_CALC_FOUND_ROWS s.*, a.name, a1.id AS agent_id FROM {$this->db->dbprefix}o2o_school AS s ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}agents AS a ON(a.id=s.agent_id) ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}agents AS a1 ON(a1.school_id=s.id) ";
            $sql .= "WHERE $where ORDER BY id DESC ";
            if($limit > 0 || $offset > 0){
               $sql .= "LIMIT $limit, $offset";
            }
            $result = $this->db->query($sql)->result_array();
            if($limit > 0 || $offset > 0){
                $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
                return array("count" => $row_num['row_num'], "result" => $result);
            }else{
                return $result;
            }
        }

        function get_school_teachers($limit = 0, $offset = 0, $conditions = array()){
            $where = "1 = 1";
            if($conditions){
                if(!empty($conditions['school_id'])){
                    $where .= " AND ot.school_id = '{$conditions['school_id']}'";
                }
            }
            
            $sql = "SELECT SQL_CALC_FOUND_ROWS ot.*, s.title FROM {$this->db->dbprefix}o2o_teachers AS ot ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}o2o_class_teacher AS t ON(t.teacher_id=ot.id) ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}b_subject AS s ON(s.id=t.subject_id) ";
            $sql .= "WHERE $where ORDER BY ot.id DESC ";
            if($limit > 0 || $offset > 0){
               $sql .= "LIMIT $limit, $offset";
            }
            $result = $this->db->query($sql)->result_array();
            $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
            return array("count" => $row_num['row_num'], "result" => $result);
        }

        function get_school_courses($limit = 0, $offset = 0, $conditions = array()){
            $where = "1 = 1";
            if($conditions){
                if(!empty($conditions['school_id'])){
                    $where .= " AND school_id = '{$conditions['school_id']}'";
                }
            }
            $sql = "SELECT SQL_CALC_FOUND_ROWS * FROM {$this->db->dbprefix}course   WHERE $where ORDER BY id DESC ";
            if($limit > 0 || $offset > 0){
               $sql .= "LIMIT $limit, $offset";
            }
            $result = $this->db->query($sql)->result_array();
            $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
            return array("count" => $row_num['row_num'], "result" => $result);
        }

        function get_school_system_topics($school_id = 0){
            $sql = "SELECT * FROM {$this->db->dbprefix}o2o_school_system_topics WHERE school_id = $school_id";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }

        function get_course_cate(){
            $sql = "SELECT * FROM {$this->db->dbprefix}course_cate WHERE school_id = 0 and type = 1";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }

        function get_school_system_video_course($school_id = 0){
            $sql = "SELECT * FROM {$this->db->dbprefix}o2o_school_system_video_course WHERE school_id = $school_id";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }
	}
